package br.com.comdoc.repositorio;

//@author Jorge
import br.com.comdoc.DAO.LinkDB;
import br.com.comdoc.entidade.Contato;
import br.com.comdoc.interfaces.InterfaceContato;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;

public class RepositorioContato implements InterfaceContato {

    private Connection conn;
    private String query;
    private PreparedStatement stmt;
    private ResultSet rs;
    private Contato contato;
    private ArrayList list = new ArrayList();

    @Override
    public void salvarContato(String nome1, String nome2, String cargo1, String cargo2,
            String telefone1, String telefone2, String email1, String email2) throws ClassNotFoundException,
            SQLException {

        contato = new Contato(nome1, nome2, cargo1, cargo2, telefone1, telefone2,
                email1, email2);

        conn = LinkDB.getConnection();

        query = "INSERT INTO contato(NOMECONTATO1, NOMECONTATO2, CARGOCONTATO1, "
                + "CARGOCONTATO2, TELEFONECONTATO1, TELEFONECONTATO2, EMAILCONTATO1, "
                + "EMAILCONTATO2) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(query);

        stmt.setString(1, contato.getNome1());
        stmt.setString(2, contato.getNome2());
        stmt.setString(3, contato.getCargo1());
        stmt.setString(4, contato.getCargo2());
        stmt.setString(5, contato.getTelefone1());
        stmt.setString(6, contato.getTelefone2());
        stmt.setString(7, contato.getEmail1());
        stmt.setString(8, contato.getEmail2());

        stmt.executeUpdate();

        conn.close();
        stmt.close();
    }

    @Override
    public void aterarContato(int id, String nome1, String nome2, String cargo1,
            String cargo2, String telefone1, String telefone2, String email1, String email2)
            throws ClassNotFoundException, SQLException {

        contato = new Contato(nome1, nome2, cargo1, cargo2, telefone1, telefone2,
                email1, email2);
        contato.setId(id);

        conn = LinkDB.getConnection();

        query = "UPDATE contatos SET NOMECONTATO1 = ?, NOMECONTATO2 = ?, CARGOCONTATO1 = ?, "
                + "CARGOCONTATO2 = ?, TELEFONE1CONTATO1 = ?, TELEFONE2CONTATO2 = ?, "
                + "EMAIL1CONTATO1 = ?, EMAIL2CONTATO2 = ? WHERE IDCONTATOS = ?";

        stmt = conn.prepareStatement(query);

        stmt.setString(1, contato.getNome1());
        stmt.setString(2, contato.getNome2());
        stmt.setString(3, contato.getCargo1());
        stmt.setString(4, contato.getCargo2());
        stmt.setString(5, contato.getTelefone1());
        stmt.setString(6, contato.getTelefone2());
        stmt.setString(7, contato.getEmail1());
        stmt.setString(8, contato.getEmail2());
        stmt.setInt(9, contato.getId());

        stmt.executeUpdate();

        JOptionPane.showMessageDialog(null, "Alterado com sucesso!");

        conn.close();
        stmt.close();

    }

    @Override
    public void deletarContato(int id)
            throws ClassNotFoundException, SQLException {

        contato = new Contato();
        contato.setId(id);

        conn = LinkDB.getConnection();
        query = "UPDATE contatos SET STATUSCONTATO = '0'"
                + "WHERE IDCONTATOS = ?";

        stmt = conn.prepareStatement(query);

        stmt.setInt(1, contato.getId());

        stmt.executeUpdate();

        JOptionPane.showMessageDialog(null, "Deletado com sucesso!");

        conn.close();
        stmt.close();

    }

    @Override
    public ArrayList consultarContatos()
            throws ClassNotFoundException, SQLException {

        conn = LinkDB.getConnection();

        query = "SELECT * FROM contatos";

        stmt = conn.prepareStatement(query);

        rs = stmt.executeQuery();

        while (rs.next()) {

            list.add(rs.getString("IDCONTATOS"));
            list.add(rs.getString("NOMECONTATO"));
            list.add(rs.getString("CARGOCONTATO"));
            list.add(rs.getString("TELEFONE1CONTATO"));
            list.add(rs.getString("TELEFONE2CONTATO"));
            list.add(rs.getString("TELEFONE3CONTATO"));
            list.add(rs.getString("EMAIL1CONTATO"));
            list.add(rs.getString("EMAIL2CONTATO"));
            list.add(rs.getString("NOMECONTATO"));
            list.add(rs.getString("STATUSCONTATO"));
        }

        JOptionPane.showMessageDialog(null, "Sim, foram achados resultados!");

        conn.close();
        stmt.close();
        rs.close();

        return list;
    }

    @Override
    public ArrayList consultarContatosPorNomeExato(String nome)
            throws ClassNotFoundException, SQLException {

        contato = new Contato();

        contato.setNome1(nome);

        conn = LinkDB.getConnection();

        query = "SELECT * FROM contatos WHERE contatos.NOMECONTATO1 = ? || contatos.NOMECONTATO2 = ?";

        stmt = conn.prepareStatement(query);
        stmt.setString(1, contato.getNome1());
        stmt.setString(2, contato.getNome2());

        rs = stmt.executeQuery();

        while (rs.next()) {

            list.add(rs.getString("IDCONTATO"));
            list.add(rs.getString("NOMECONTATO1"));
            list.add(rs.getString("NOMECONTATO2"));
            list.add(rs.getString("CARGOCONTATO1"));
            list.add(rs.getString("CARGOCONTATO2"));
            list.add(rs.getString("TELEFONECONTATO1"));
            list.add(rs.getString("TELEFONECONTATO2"));
            list.add(rs.getString("EMAILCONTATO1"));
            list.add(rs.getString("EMAILCONTATO2"));

        }

        conn.close();
        stmt.close();
        rs.close();

        return list;
    }

    @Override
    public ArrayList consultarContatosPorNomeConcat(String nomeConcat)
            throws ClassNotFoundException, SQLException {
        contato = new Contato();

        contato.setNome1(nomeConcat);

        conn = LinkDB.getConnection();

        query = "SELECT * FROM contatos WHERE contatos.NOMECONTATO1 LIKE CONCAT('%', ?, '%') || "
                + "contatos.NOMECONTATO2 LIKE CONCAT('%', ?, '%')";

        stmt = conn.prepareStatement(query);
        stmt.setString(1, contato.getNome1());
        stmt.setString(2, contato.getNome2());

        rs = stmt.executeQuery();

        while (rs.next()) {

            list.add(rs.getString("IDCONTATO"));
            list.add(rs.getString("NOMECONTATO1"));
            list.add(rs.getString("NOMECONTATO2"));
            list.add(rs.getString("CARGOCONTATO1"));
            list.add(rs.getString("CARGOCONTATO2"));
            list.add(rs.getString("TELEFONECONTATO1"));
            list.add(rs.getString("TELEFONECONTATO2"));
            list.add(rs.getString("EMAILCONTATO1"));
            list.add(rs.getString("EMAILCONTATO2"));
        }

        conn.close();
        stmt.close();
        rs.close();

        return list;
    }

    @Override
    public int returnIdContato() throws ClassNotFoundException, SQLException {

        conn = LinkDB.getConnection();

        query = "SELECT * FROM contato ORDER BY contato.IDCONTATO DESC LIMIT 0, 1;";

        stmt = conn.prepareStatement(query);

        rs = stmt.executeQuery();

        if (rs.next()) {
            String id = rs.getString("contato.IDCONTATO");
            int retornoInteiro = Integer.parseInt(id);
            return retornoInteiro;
        } else {
            return 0;
        }
    }
}
